1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to parallel optimized triggers in a parallel processing database system.
2. Description of Related Art
Relational DataBase Management Systems (RDBMS) store data into tables. A table in a relational database is two dimensional, comprising rows and columns. Each column has a name, typically describing the type of data held in that column. As new data is added, more rows are inserted into the table. A user query selects some rows of the table by specifying clauses in SQL (Structured Query Language) statements that qualify the rows to be retrieved based on the values in one or more of the columns.
Database triggers are defined in the 1995 SQL3 ANSI standard for relational database management systems. Triggers are event-driven, specialized procedures attached to a subject table that define some action to take, called a triggered action, when some event, called a triggering event, occurs. Generally, triggering events involve an INSERT, UPDATE or DELETE operation that modifies one or more specified columns in the subject table. Similarly, triggered actions generally involve INSERT, INSERT-SELECT, UPDATE, DELETE, ABORT, ROLLBACK, BEGIN-END, or EXECUTE statements.
There are three types of trigger action times: BEFORE, AFTER and INSTEAD OF. Depending on the trigger action time, a triggered statement xe2x80x9cfiresxe2x80x9d a trigger before, after, or instead of the triggering action. When a triggered statement fires a trigger, a phenomenon called cascading may ensue. In cascading, triggered statements cause trigger events, which result in the execution of other triggered statements. This sequence can repeat and continue until the cascading normally or abnormally ends.
With the advent of data warehouses, it is not uncommon for relational databases to store very large tables. Such tables may range from megabytes to gigabytes, terabytes, or more. As a result, the RDBMS may have to examine thousands, millions, billions, or more, records in the course of one or more triggering events. Moreover, since the triggering event usually relates to records where an INSERT, UPDATE or DELETE operation has modified one or more specified columns in the subject table, it is usually the case where the triggered actions also relate to these records. In the prior art, the necessary records would be retrieved from the table for the triggering event and then again for the triggered action.
Often, however, it may be possible to reduce the number of times a record is retrieved for triggering events and triggered actions. The advantage, of course, is that the table size and record counts for the subsequent triggered actions could be greatly reduced. This would result in faster execution using fewer resources, thereby improving response time and data throughput.
While there have been various techniques developed for optimizing the performance of RDBMS, there is a need in the art for techniques that optimize the performance of database triggers by reducing the size of very large tables and performing the triggers in parallel.
The present invention discloses a method, apparatus, and article of manufacture for accessing a subject table in a computer system. The subject table is partitioned across a plurality of processing units of the computer system. A trigger is defined for the subject table, such that when a triggering event occurs on the subject table, a triggered action is performed. Both the triggering event and triggered action can be performed in parallel by the computer system. A spool table is generated for the necessary rows accessed from the subject table by the triggering event. In this manner, triggered actions that refer to these necessary rows need not access the subject table again.
An object of the present invention is to optimize the database access on parallel processing computer systems. Another object of the present invention is to improve the performance of database partitions managed by a parallel processing computer systems.